<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
SELECT
t.*, (
CASE
WHEN (
EXISTS
(SELECT
1
FROM
c1_role_user e
WHERE e.role_id = t.id
AND e.user_id = :userId)
OR EXISTS
(SELECT
1
FROM
c1_us_group_role e
WHERE e.role_id = t.id
AND EXISTS
(SELECT
1
FROM
c1_us_group_user e_e
WHERE e_e.user_group_id = e.user_group_id
AND e_e.user_id = :userId))
)
THEN 'y'
ELSE 'n'
END
) AS has_user
FROM
c1_role t
<#if StringUtils.isNotBlank(orgId)>
WHERE t.`ORG_ID` = :orgId
<#else>
WHERE t.`ORG_ID` IS NULL OR t.`ORG_ID` = ''
</#if>
ORDER BY t.org_id, t.department_id, t.order_num
